package com.hnqcgczy.sgzShop.dao.impl;

import com.hnqcgczy.sgzShop.dao.IProductDao;
import com.hnqcgczy.sgzShop.db.MyTransaction;
import com.hnqcgczy.sgzShop.vo.TProduct;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements IProductDao {
    private MyTransaction myTransaction;

    public ProductDaoImpl(MyTransaction myTransaction) {
        this.myTransaction = myTransaction;
    }

    @Override
    public List<TProduct> listAllProduct() throws SQLException {
        //1、获得数据库连接
        Connection connection = myTransaction.getConnection();
        //2、发送sql语句
        String sql="select * from t_product";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3、执行sql语句
        ResultSet rs = ps.executeQuery();
        List<TProduct> list=new ArrayList<>();
        //4、处理查询结果
        while (rs.next()){
            TProduct tProduct=new TProduct();
            tProduct.setTpId(rs.getInt(1));
            tProduct.setTpName(rs.getString(2));
            tProduct.setTpDescription(rs.getString(3));
            tProduct.setTpPrice(rs.getDouble(4));
            tProduct.setTpStock(rs.getInt(5));
            tProduct.setTpcId(rs.getInt(6));
            tProduct.setTpcChildId(rs.getInt(7));
            tProduct.setTpFileName(rs.getString(8));
            list.add(tProduct);
        }
        //5、释放资源 将数据库连接放回连接池
        rs.close();
        ps.close();
        myTransaction.closeConn();

        return list;
    }

    @Override
    public List<TProduct> queryAnyProduct(TProduct product,int page, int pageSize) throws SQLException {
        //1、获得数据库连接
        Connection connection = myTransaction.getConnection();
        //准备sql语句
        StringBuffer stringBuffer=new StringBuffer("select * from t_product where 1=1");
        if(!product.getTpName().equals("")){
            stringBuffer.append(" and tp_name like ?");
        }
        if(!product.getTpDescription().equals("")){
            stringBuffer.append(" and tp_description like ?");

        }
        stringBuffer.append(" limit ?,?");
        //2、发送sql语句
        PreparedStatement ps = connection.prepareStatement(stringBuffer.toString());
        //给？注入值
        ps.setString(1,"%"+product.getTpName()+"%");
        ps.setString(2,"%"+product.getTpDescription()+"%");
        ps.setInt(3,(page-1)*pageSize);
        ps.setInt(4,pageSize);
        //3、执行sql语句
        ResultSet rs = ps.executeQuery();
        List<TProduct> list=new ArrayList<>();
        //4、处理查询结果
        while (rs.next()){
            TProduct tProduct=new TProduct();
            tProduct.setTpId(rs.getInt(1));
            tProduct.setTpName(rs.getString(2));
            tProduct.setTpDescription(rs.getString(3));
            tProduct.setTpPrice(rs.getDouble(4));
            tProduct.setTpStock(rs.getInt(5));
            tProduct.setTpcId(rs.getInt(6));
            tProduct.setTpcChildId(rs.getInt(7));
            tProduct.setTpFileName(rs.getString(8));
            list.add(tProduct);
        }
        //5、释放资源 将数据库连接放回连接池
        rs.close();
        ps.close();
        myTransaction.closeConn();

        return list;
    }

    @Override
    public TProduct querById(int tp_id) throws SQLException {
        //1、获得数据库连接
        Connection connection = myTransaction.getConnection();
        //2、发送sql语句
        String sql="select * from t_product where tp_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,tp_id);
        //3、执行sql语句
        ResultSet rs = ps.executeQuery();
        TProduct tProduct=null;
        //4、处理查询结果
        if (rs.next()){
            tProduct=new TProduct();
            tProduct.setTpId(rs.getInt(1));
            tProduct.setTpName(rs.getString(2));
            tProduct.setTpDescription(rs.getString(3));
            tProduct.setTpPrice(rs.getDouble(4));
            tProduct.setTpStock(rs.getInt(5));
            tProduct.setTpcId(rs.getInt(6));
            tProduct.setTpcChildId(rs.getInt(7));
            tProduct.setTpFileName(rs.getString(8));

        }
        //5、释放资源 将数据库连接放回连接池
        rs.close();
        ps.close();
        myTransaction.closeConn();
        return tProduct;
    }

    public static void main(String[] args) {
        ProductDaoImpl productDao = new ProductDaoImpl(new MyTransaction(true));
        TProduct t=new TProduct();
        t.setTpName("黄桃");
        t.setTpDescription("炎");
        try {
            List<TProduct> list = productDao.queryAnyProduct(t, 2, 3);
            System.out.println(list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
